package com.kaibes.imserver.model.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.kaibes.imserver.KaibesIMServer;
import com.kaibes.imserver.model.StartButtonListenerModel;
import com.kaibes.imserver.view.ServerMainView;

public abstract class DatabaseHelper {

	private String driver = "com.mysql.jdbc.Driver";
	protected String url = "jdbc:mysql://127.0.0.1:3306/";
	protected String dbsqluser = "root";
	protected String dbpassword = "root";
	private Connection connection = null;
	protected PreparedStatement preparedStatement = null;

	public DatabaseHelper(String database) {
		connect(database);
	}

	public void changeDatabase(String database) {
		try {
			preparedStatement = connection.prepareStatement("use ?");
			preparedStatement.setString(1, database);
			preparedStatement.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void connect(String database) {
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url + database, dbsqluser,
					dbpassword);
			if (connection == null) {
				StartButtonListenerModel.flag = false;
				KaibesIMServer.mainClientControl.closeServer();
				StartButtonListenerModel.jb
						.setText("\u542f\u52a8\u670d\u52a1\u5668");
			}
			if (!connection.isClosed()) {
				ServerMainView.serverPanelView.panel_data.addLog(this
						+ ": Succeeded connecting to the Database " + database);

			}
		} catch (ClassNotFoundException e) {
			ServerMainView.serverPanelView.panel_data.addLog(this
					+ ": Sorry,can`t find the Driver!");
			// e.printStackTrace();
		} catch (SQLException e) {
			ServerMainView.serverPanelView.panel_data.addLog(this
					+ ": Sorry,mysql put off!");
			// e.printStackTrace();
		}
	}

	public void close() {
		try {
			// ServerMainView.serverPanelView.panel_data.addLog(this+": Closed connecting to the Database "+database);
			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	protected ResultSet leftJoin(String table_1, String table_2,
			String columns, String selectionJ, String selection,
			Object[] selectionArgs) {

		String sql = String.format(
				"select %s from %s as a left join %s as b on (%s) where %s",
				columns, table_1, table_2, selectionJ, selection);
		ResultSet result = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < selectionArgs.length; i++) {
				setObject(i, selectionArgs[i]);
			}
			result = preparedStatement.executeQuery();
			return getResultSet(result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	protected int delete(String table, String selection, Object[] values) {
		String sql = String.format("delete from %s where %s", table, selection);
		try {
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < values.length; i++) {
				setObject(i, values[i]);
			}
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	protected int update(String table, String columns, String selection,
			Object[] values, Object[] selectionArgs) {
		String sql = String.format("update %s set %s where %s", table, columns,
				selection);
		try {
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < values.length; i++) {
				setObject(i, values[i]);
			}
			for (int i = 0; i < selectionArgs.length; i++) {
				setObject(values.length + i, selectionArgs[i]);
			}
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 这里我认为所有的values都是string，实际上是不可能的，但是却可以修改数据表而达到所有values都是string,
	 * 但如果要使用string以外的数据类型，可以通过使用hashmap记录数据位置的方法重写这个方法，这里就不写了
	 * 
	 * @author QiuShuiCai
	 * @return int
	 * @throws SQLException
	 */
	protected int insert(String table, String columns, Object[] values)
			throws SQLException {
		String valueString = "";
		for (int i = 0; i < values.length - 1; i++) {
			valueString += "?,";
		}
		valueString += "?";
		String sql = String.format("insert into %s(%s) values(%s)", table,
				columns, valueString);
		preparedStatement = connection.prepareStatement(sql);
		for (int i = 0; i < values.length; i++) {
			setObject(i, values[i]);
		}
		return preparedStatement.executeUpdate();
	}

	protected ResultSet select(String table, String columns, String selections,
			Object[] selectionArgs) {
		String sql = String.format("select %s from %s where %s", columns,
				table, selections);
		ResultSet result = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < selectionArgs.length; i++) {
				setObject(i, selectionArgs[i]);
			}
			result = preparedStatement.executeQuery();
		} catch (SQLException e) {
			System.err.println("数据库没有打开？");
			e.printStackTrace();
		}
		return getResultSet(result);
	}

	private ResultSet getResultSet(ResultSet result) {
		try {
			if (result.first()) {
				result.absolute(0);
			} else {
				return null;
			}
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	protected PreparedStatement getPreparedStatement(String sql) {
		try {
			return connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	private void setObject(int i, Object value) {
		try {
			if (value instanceof Integer) {
				preparedStatement.setInt(i + 1, (Integer) value);
			} else {
				preparedStatement.setString(i + 1, (String) value);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
